﻿CREATE PROCEDURE [dbo].[spAlarm_GetServerPLE]
(
	@serverName					sysname	= NULL,
	@startDate					date	= NULL,
	@endDate					date	= NULL,
	@onlyDaysWithBadPLE			bit		= 0
)
AS

--- Ensure that there is a value for both start and end date
IF @startDate IS NULL AND @endDate IS NULL
BEGIN
	SET	@endDate = DATEADD(dd, 1, (CAST(GETDATE() as date)))
	SET @startDate = DATEADD(month, DATEDIFF(month, 0, @endDate), 0)
END
ELSE IF @startDate IS NULL
	SET @startDate = DATEADD(month, DATEDIFF(month, 0, @endDate), 0)
ELSE IF @endDate IS NULL
	SET	@endDate = DATEADD(dd, 1, @startDate)
ELSE IF @endDate IS NOT NULL
	SET	@endDate = DATEADD(dd, 1, @endDate) --- adding a day so that the requested date is part of the results

SELECT		[ssd_sqlserver_name_ro],
			[date_inserted],
			[minBufferCacheHitRatio],
			[minGoodPLE],
			[minPLE],
			CAST([25P] as int) [25P],
			CAST([50P] as int) [50P],
			[avgPLE],
			CAST([75P] as int) [75P],
			[maxPLE]
FROM		(
			SELECT		DISTINCT
						t2.[ssd_sqlserver_name_ro],
						CAST([date_inserted] as date) [date_inserted],
						MIN([buffer_cache_hit_ratio]) OVER (PARTITION BY t2.[ssd_sqlserver_name_ro], CAST([date_inserted] as date)) [minBufferCacheHitRatio],
						(target_server_memory_KB * 300 / 1024 / 4096) [minGoodPLE],		--- 300 per 4 GB (https://simplesqlserver.com/2013/08/19/fixing-page-life-expectancy-ple/)
						MIN([page_life_expectancy]) OVER (PARTITION BY t2.[ssd_sqlserver_name_ro], CAST([date_inserted] as date)) [minPLE],
						PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [page_life_expectancy]) OVER (PARTITION BY t2.[ssd_sqlserver_name_ro], CAST([date_inserted] as date)) [25P],
						PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [page_life_expectancy]) OVER (PARTITION BY t2.[ssd_sqlserver_name_ro], CAST([date_inserted] as date)) [50P],
						AVG([page_life_expectancy]) OVER (PARTITION BY t2.[ssd_sqlserver_name_ro], CAST([date_inserted] as date)) [avgPLE],
						PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY [page_life_expectancy]) OVER (PARTITION BY t2.[ssd_sqlserver_name_ro], CAST([date_inserted] as date)) [75P],
						MAX([page_life_expectancy]) OVER (PARTITION BY t2.[ssd_sqlserver_name_ro], CAST([date_inserted] as date)) [maxPLE]
			FROM		[dbo].[tblWH_Perfmon] t1
			JOIN		[dbo].[tblMSX_server_discovery] t2 ON t1.[server_ssd_id] = t2.[ssd_id]
						AND t2.[ssd_is_disabled] = 0
			WHERE		t1.[date_inserted] >= @startDate
						AND t1.[date_inserted] < @endDate
						AND (
							@serverName IS NULL
							OR (
								@serverName IS NOT NULL
								AND t2.[ssd_sqlserver_name_ro] = @serverName
							)
						)
			) t1
WHERE		@onlyDaysWithBadPLE = 0
			OR (
				@onlyDaysWithBadPLE = 1
				AND [25P] <= [minGoodPLE]
			)
